import os

import xlsxwriter as xlsxwriter
from django.conf import settings
from django.db.models import Q
from django.http import HttpResponseRedirect
from django.shortcuts import render
from django.views.decorators.csrf import csrf_protect

from app.forms import OrderAnalysisForm, ORDER_STATUS_CHOICES
from app.models import Order


@csrf_protect
def order_analysis(request):
    if request.method == 'POST':
        form = OrderAnalysisForm(request.POST)
        if form.is_valid():
            cd = form.cleaned_data
            datetime_from = cd['datetime_from']
            datetime_to = cd['datetime_to']
            order_status = cd['order_status']
            area = cd['area']
            sales = cd['sales']
            user = cd['user']
            q = {}
            if order_status:
                q['status'] = order_status
            if user:
                q['user'] = user
            elif sales:
                q['user__sales'] = sales
            elif area:
                q['user__sales__sales_area'] = area
            query_set = Order.objects.filter(**q)\
                .filter(Q(delivery_datetime__range=(datetime_from, datetime_to)) |
                        Q(delivery_deadline_datetime__range=(datetime_from, datetime_to)))

            the_file_name = "order.xlsx"
            write_excel(os.path.join(settings.MEDIA_ROOT, the_file_name), query_set)
            return HttpResponseRedirect(os.path.join(settings.MEDIA_URL, the_file_name))
    else:
        form = OrderAnalysisForm()
    return render(request, 'app/order_analysis.html', {'form': form, 'order_status_choices': ORDER_STATUS_CHOICES})


def file_iterator(file_name, chunk_size=512):
    with open(file_name) as f:
        while True:
            c = f.read(chunk_size)
            if c:
                yield c
            else:
                break


def analysis_order(order_list):
    result = {}
    for order in order_list:
        for product in order.products.all():
            key = (product.product_name, product.spec_name, product.price, product.unit,)
            value = product.amount
            if key in result:
                result[key] += value
            else:
                result[key] = value
    return result


def write_excel(file_name, order_list):
    with xlsxwriter.Workbook(file_name) as workbook:  # 建立文件
        # 所有商品汇总
        write_products(workbook, analysis_order(order_list))
        # 所有订单汇总
        write_orders(workbook, order_list)
        # 按用户写入
        write_order_by_user(workbook, order_list)


def write_products(workbook, products):
    bold = workbook.add_format({'bold': 1})
    products_ws_titles = [u'商品名称', u'规格名称', u'单价（元）', u'单位', u'总量', u'价格总计（元）']
    products_ws = workbook.add_worksheet(u'products')  # 建立sheet，可以work.add_worksheet('employee')来指定sheet名
    for i, title in enumerate(products_ws_titles):
        products_ws.write(0, i, title, bold)
    row = 1
    for (k, v) in products.items():
        products_ws.write(row, 0, k[0])  # A product_name
        products_ws.write(row, 1, k[1])  # B spec_name
        products_ws.write(row, 2, k[2] / 100.0)  # C price
        products_ws.write(row, 3, k[3])  # D unit
        products_ws.write(row, 4, v)  # E amount
        products_ws.write(row, 5, "=C%d*E%d" % (row + 1, row + 1))  # =sum()
        row += 1
    if row > 2:
        products_ws.write(row, 5, "=sum(F2:F%d)" % row)


def write_orders(workbook, order_list):
    bold = workbook.add_format({'bold': 1})
    orders_ws = workbook.add_worksheet(u'orders')
    orders_ws_titles = [u'ID', u'下单用户', u'金额（元）', u'订单状态',
                        u'订单生成时间', u'订单过期时间', u'邮寄地址',
                        u'联系方式', u'配送时间', u'备注',
                        u'订单号', u'销售', u'支付方式']
    for i, title in enumerate(orders_ws_titles):
        orders_ws.write(0, i, title, bold)
    for i, order in enumerate(order_list, 1):
        orders_ws.write(i, 0, order.id)
        orders_ws.write(i, 1, order.user.merchant_name)
        orders_ws.write(i, 2, order.sum / 100.0)
        orders_ws.write(i, 3, order.get_status_display())
        if order.created_datetime:
            orders_ws.write(i, 4, order.created_datetime.strftime('%Y-%m-%d %H:%M:%S'))
        if order.expired_datetime:
            orders_ws.write(i, 5, order.expired_datetime.strftime('%Y-%m-%d %H:%M:%S'))
        if order.address:
            orders_ws.write(i, 6, str(order.address))
            orders_ws.write(i, 7, order.address.phone)
        delivery_datetime_str = ""
        if order.delivery_datetime:
            delivery_datetime_str += order.delivery_datetime.strftime('%Y-%m-%d %H:%M:%S')
        if order.delivery_deadline_datetime:
            delivery_datetime_str += ' - ' + order.delivery_deadline_datetime.strftime('%Y-%m-%d %H:%M:%S')
        orders_ws.write(i, 8, delivery_datetime_str)
        orders_ws.write(i, 9, order.remark)
        orders_ws.write(i, 10, order.out_order_id)
        if order.user and order.user.sales:
            orders_ws.write(i, 11, order.user.sales.username)
        if order.pay_method:
            orders_ws.write(i, 12, order.pay_method.name)

        for (j, product) in enumerate(order.products.all(), 13):
            orders_ws.write(i, j, "%s-%s-%d-%.2f-%.2f" % (product.product_name, product.spec_name, product.amount,
                                                          product.price / 100.0,
                                                          (product.amount * product.price / 100.0)))


def write_order_by_user(workbook, order_list: [Order]):
    user_order = {}
    for order in order_list:
        if order.user in user_order:
            user_order[order.user].append(order)
        else:
            user_order[order.user] = [order]
    for (user, orders) in user_order.items():
        user_ws = workbook.add_worksheet(user.merchant_name)
        user_ws.set_column('A:A', 10)
        user_ws.set_column('B:C', 18)
        user_ws.set_column('D:E', 6)
        user_ws.set_column('G:G', 10)
        row = 1
        for order in orders:
            title_format = workbook.add_format({'bold': True, 'align': 'center', 'font_size': 18})
            user_ws.merge_range('A%d:G%d' % (row, row), u'ID48华焱农副产品配送', title_format)
            row += 1
            format2 = workbook.add_format({'bold': True, 'align': 'center', 'font_size': 12})
            user_ws.merge_range('A%d:G%d' % (row, row), order.user.merchant_name, format2)
            row += 1
            format3 = workbook.add_format({'bold': True, 'font_size': 18})
            user_ws.merge_range('A%d:G%d' % (row, row), order.remark, format3)
            row += 1
            user_ws.set_row(row - 1, 20)
            format4 = workbook.add_format({'bold': True, 'font_size': 14})
            delivery_datetime_str = ""
            if order.delivery_datetime:
                delivery_datetime_str += order.delivery_datetime.strftime('%Y-%m-%d %H:%M:%S')
            if order.delivery_deadline_datetime:
                delivery_datetime_str += ' - ' + order.delivery_deadline_datetime.strftime('%Y-%m-%d %H:%M:%S')
            user_ws.merge_range('A%d:G%d' % (row, row),
                                u'【配送时间】%s' % delivery_datetime_str,
                                format4)
            row += 1
            user_ws.set_row(row - 1, 20)
            format5 = workbook.add_format({'bold': True, 'align': 'vcenter', 'font_size': 9, 'bottom': 1})
            if order.created_datetime:
                user_ws.merge_range('A%d:F%d' % (row, row),
                                    u'【下单时间】%s' % order.created_datetime.strftime('%Y-%m-%d %H:%M:%S'),
                                    format5)
            format10 = workbook.add_format({'bold': True, 'font_size': 11, 'bottom': 1})
            if order.pay_method:
                user_ws.write('G%d' % row, order.pay_method.name, format10)
            else:
                user_ws.write('G%d' % row, '', format10)
            format6 = workbook.add_format({'bold': True, 'font_size': 11, 'text_wrap': True})
            format7 = workbook.add_format({'bold': True, 'align': 'center', 'font_size': 11, 'text_wrap': True})
            row += 1
            user_ws.set_row(row-1, 30)
            user_ws.write('B%d' % row, u'商品', format6)
            user_ws.write('C%d' % row, u'规格', format6)
            user_ws.write('D%d' % row, u'单价(元)', format7)
            user_ws.write('E%d' % row, u'数量', format7)
            user_ws.write('F%d' % row, u'单位', format7)
            user_ws.write('G%d' % row, u'总价(元)', format7)
            row += 1
            products = order.products.all()
            for product in products:
                user_ws.write('B%d' % row, product.product_name, format6)
                user_ws.write('C%d' % row, product.spec_name, format6)
                user_ws.write('D%d' % row, product.price / 100.0, format7)
                user_ws.write('E%d' % row, product.amount, format7)
                user_ws.write('F%d' % row, product.unit, format7)
                user_ws.write('G%d' % row, '=D%d*E%d' % (row, row), format7)
                row += 1
            user_ws.set_row(row - 1, 20)
            format9 = workbook.add_format({'top': 1, 'bottom': 1})
            user_ws.merge_range('A%d:G%d' % (row, row), '', format9)
            row += 1
            format8 = workbook.add_format({'bold': True, 'text_wrap': True, 'font_size': 16})
            user_ws.write('B%d' % row, u'总计：', format8)
            if len(products) > 0:
                money_format = workbook.add_format({'bold': True, 'text_wrap': True, 'font_size': 16,
                                                    'num_format': '￥#,##0.00'})
                user_ws.write('C%d' % row, '=sum(G%d:G%d)' % (row - 1 - len(products), row - 2), money_format)
            row += 1
            if order.address:
                user_ws.merge_range('A%d:E%d' % (row, row), str(order.address), format8)
                user_ws.merge_range('F%d:G%d' % (row, row), order.address.phone, format8)
            row += 1
            user_ws.set_row(row - 1, 20)
            user_ws.write('A%d' % row, u'销售顾问', format6)
            if order.user.sales:
                user_ws.write('B%d' % row, order.user.sales.realname, format6)  # 姓名
                user_ws.write('C%d' % row, order.user.sales.username, format6)  # 手机号
            row += 1
